﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using Common.Utilities;
using Workflows.Components.TextMining.Collapse;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;

namespace Workflows.Components.TextMining.Clustering
{
    /// <summary>
    /// 
    /// </summary>
    /// <param name="docTermListXmlFilePath"></param>
    public delegate void DocTermListFileUpdated(string docTermListXmlFilePath);

    /// <summary>
    /// 
    /// </summary>
    public partial class DocumentTermProvider : Form
    {
        public event DocTermListFileUpdated OnDocTermListFileUpdated;

        /// <summary>
        /// 
        /// </summary>
        public DocumentTermProvider()
        {
            InitializeComponent();
        }

        private void DocumentTermProvider_Load(object sender, EventArgs e)
        {

        }

        private void btn_Browse_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg=new OpenFileDialog();
            dlg.Filter = "Excel files(*.xls;*.xlsx)|*.xls;*.xlsx|All files(*.*)|*.*";
            if(dlg.ShowDialog()==DialogResult.OK)
            {
                this.txt_ExcelFilePath.Text = dlg.FileName;
                try
                {
                    int startingRowIndex = 1;
                    Dictionary<string, int> colHeaders = 
                        WorksheetHelper.GetColumnHeaders(dlg.FileName, 1, ref startingRowIndex);
                    if(colHeaders !=null && colHeaders.Count>0)
                    {
                        this.cbo_DocIDField.Items.Clear();
                        this.cbo_TermIDField.Items.Clear();
                        this.cbo_TermField.Items.Clear();
                        this.cbo_TermWeight.Items.Clear();
                        this.cbo_DocLabelField.Items.Clear();
                        this.cbo_DocContentField.Items.Clear();
                        foreach(string colHeader in colHeaders.Keys)
                        {
                            this.cbo_DocIDField.Items.Add(colHeader);
                            this.cbo_TermIDField.Items.Add(colHeader);
                            this.cbo_TermField.Items.Add(colHeader);
                            this.cbo_TermWeight.Items.Add(colHeader);
                            this.cbo_DocLabelField.Items.Add(colHeader);
                            this.cbo_DocContentField.Items.Add(colHeader);
                        }
                    }
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void rd_Excel_CheckedChanged(object sender, EventArgs e)
        {
            if(this.rd_Excel.Checked)
            {
                this.txt_ExcelFilePath.Enabled = true;
                this.btn_Browse.Enabled = true;
                this.txt_ConnStr.Enabled = false;
                this.rt_Sql.Enabled = false;
                this.btn_Refresh.Enabled = false;
            }
            else
            {
                this.txt_ExcelFilePath.Enabled = false;
                this.btn_Browse.Enabled = false;
                this.txt_ConnStr.Enabled = true;
                this.rt_Sql.Enabled = true;
                this.btn_Refresh.Enabled = true;
            }
        }

        private void rd_Db_CheckedChanged(object sender, EventArgs e)
        {
            if (this.rd_Excel.Checked)
            {
                this.txt_ExcelFilePath.Enabled = true;
                this.btn_Browse.Enabled = true;
                this.txt_ConnStr.Enabled = false;
                this.rt_Sql.Enabled = false;
                this.btn_Refresh.Enabled = false;
            }
            else
            {
                this.txt_ExcelFilePath.Enabled = false;
                this.btn_Browse.Enabled = false;
                this.txt_ConnStr.Enabled = true;
                this.rt_Sql.Enabled = true;
                this.btn_Refresh.Enabled = true;
            }
        }

        private void btn_Refresh_Click(object sender, EventArgs e)
        {
            if(!string.IsNullOrEmpty(this.txt_ConnStr.Text) && 
                !string.IsNullOrEmpty(this.rt_Sql.Text))
            {
                TableColumn[] cols =
                    DbUtil.GetTableColumns(DataProviderType.Oracle,
                                           this.txt_ConnStr.Text,
                                           this.rt_Sql.Text);
                this.cbo_DocIDField.Items.Clear();
                this.cbo_TermIDField.Items.Clear();
                this.cbo_TermField.Items.Clear();
                this.cbo_TermWeight.Items.Clear();
                this.cbo_DocLabelField.Items.Clear();
                this.cbo_DocContentField.Items.Clear();

                if(cols !=null && cols.Length>0)
                {
                    foreach(TableColumn col in cols)
                    {
                        this.cbo_DocIDField.Items.Add(col.ColumnName);
                        this.cbo_TermIDField.Items.Add(col.ColumnName);
                        this.cbo_TermField.Items.Add(col.ColumnName);
                        this.cbo_TermWeight.Items.Add(col.ColumnName);
                        this.cbo_DocLabelField.Items.Add(col.ColumnName);
                        this.cbo_DocContentField.Items.Add(col.ColumnName);
                    }
                }
            }
        }

        private void btn_OK_Click(object sender, EventArgs e)
        {
            if(this.rd_Excel.Checked)
            {
                if(string.IsNullOrEmpty(this.txt_ExcelFilePath.Text) || !File.Exists(this.txt_ExcelFilePath.Text))
                {
                    MessageBox.Show("Excel file could not be found");
                    return;
                }
            }
            else
            {
                if(string.IsNullOrEmpty(this.txt_ConnStr.Text) || 
                    string.IsNullOrEmpty(this.rt_Sql.Text))
                {
                    MessageBox.Show("Please specify database connection and sql selection statement");
                    return;
                }
            }

            if(!this.IsComboxSelected(this.cbo_DocIDField,"document ID") || 
                !this.IsComboxSelected(this.cbo_TermIDField,"term ID") || 
                !this.IsComboxSelected(this.cbo_TermWeight, "term weight") || 
                !this.IsComboxSelected(this.cbo_TermField,"term"))
            {
                return;
            }
            string appFolderPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
            string dataFolderPath = Path.Combine(appFolderPath, "OPASI");
            if (!Directory.Exists(dataFolderPath))
                Directory.CreateDirectory(dataFolderPath);
            string termListFileName = DateTime.Now.Ticks.ToString();
            string termListFile = Path.Combine(dataFolderPath, termListFileName +".xml");
            if(this.rd_Excel.Checked)
            {
                int startingRowIndex = 1;
                Dictionary<string, int> colHeaderIndexes =
                    WorksheetHelper.GetColumnHeaders(
                        this.txt_ExcelFilePath.Text, 1, ref startingRowIndex);
                int termIDColIndex = colHeaderIndexes[this.cbo_TermIDField.Text];
                int docIDColIndex = colHeaderIndexes[this.cbo_DocIDField.Text];
                int termColIndex = colHeaderIndexes[this.cbo_TermField.Text];
                int termWeightColIndex = colHeaderIndexes[this.cbo_TermWeight.Text];
                int docLabelColIndex = 0;
                if(this.cbo_DocLabelField.SelectedIndex>=0)
                {
                    docLabelColIndex = colHeaderIndexes[this.cbo_DocLabelField.Text];
                }
                int docContentColIndex = 0;
                if(this.cbo_DocContentField.SelectedIndex>=0)
                {
                    docContentColIndex = colHeaderIndexes[this.cbo_DocContentField.Text];
                }
                this.GenerateTermListFileFromExcel(
                    termListFile,this.txt_ExcelFilePath.Text,
                    startingRowIndex, docIDColIndex,termIDColIndex,
                    termColIndex,termWeightColIndex,
                    docLabelColIndex,docContentColIndex);

                if(this.OnDocTermListFileUpdated !=null)
                {
                    this.OnDocTermListFileUpdated(termListFile);   
                }
                this.Close();
            }
            else
            {
                MessageBox.Show("Selecting term list from database is not supported!");
                this.rd_Excel.Checked = true;
            }
        }

        #region xml file from excel 
        private void GenerateTermListFileFromExcel(
            string tgtXmlFilePath,
            string excelFilePath,
            int startingRowIndex,
            int docIDColIndex,
            int termIDColIndex,
            int termColIndex,
            int termWeightColIndex,
            int docLabelColIndex,
            int docContentColIndex)
        {
            if(File.Exists(tgtXmlFilePath))
                File.Delete(tgtXmlFilePath);

            Application excelApp = new Application();
            excelApp.Visible = true;
            Workbook excelWorkbook =
                excelApp.Workbooks.Open(
                    excelFilePath, 0, false, 5, "", "", true,
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                    "\t", false, false, 0, true, false, false);
            excelApp.DisplayAlerts = false;
            Worksheet sheet = (Worksheet)excelWorkbook.Worksheets[1];
            XmlDocument xDoc = new XmlDocument();
            xDoc.LoadXml("<Docs></Docs>");
            XmlNode root = xDoc.DocumentElement;

            bool canContinue = true;
            int rowIndex = startingRowIndex;
            while(canContinue)
            {
                if(WorksheetHelper.GetCell(sheet, docIDColIndex, rowIndex).Value2==null)
                {
                    canContinue = false;
                    break;
                }
                string docID = WorksheetHelper.GetCell(sheet, docIDColIndex, rowIndex).Value2.ToString();
                if(string.IsNullOrEmpty(docID))
                {
                    canContinue = false;
                    break;
                }
                XmlNode docNode = root.SelectSingleNode("Doc[@DocID=\"" + docID + "\"]");
                if(docNode==null)
                {
                    docNode = XmlDataUtil.AddElement(ref xDoc, root, "Doc");
                    XmlDataUtil.UpdateAttribute(ref xDoc, docNode, "DocID", docID);
                    string docLabel = string.Empty;
                    if (docLabelColIndex > 0)
                    {
                        docLabel = WorksheetHelper.GetCell(sheet, docLabelColIndex, rowIndex).Value2.ToString();
                    }
                    XmlDataUtil.UpdateAttribute(ref xDoc, docNode, "DocLabel", docLabel);
                    string docContent = string.Empty;
                    if(docContentColIndex >0)
                    {
                        docContent = WorksheetHelper.GetCell(sheet, docContentColIndex, rowIndex).Value2.ToString();
                    }
                    XmlDataUtil.AddCdataElement(ref xDoc, docNode, "Content", docContent);
                }
                
                if(WorksheetHelper.GetCell(sheet, termIDColIndex, rowIndex).Value2==null)
                {
                    canContinue = false;
                    break;
                }
                string termID = WorksheetHelper.GetCell(sheet, termIDColIndex, rowIndex).Value2.ToString();
                if(string.IsNullOrEmpty(termID))
                {
                    canContinue = false;
                    break;
                }
                XmlNode termNode = XmlDataUtil.AddElement(ref xDoc, docNode, "Term");
                XmlDataUtil.UpdateAttribute(ref xDoc, termNode,"TermID",termID);
                string term = WorksheetHelper.GetCell(sheet, termColIndex, rowIndex).Value2.ToString();
                XmlDataUtil.UpdateAttribute(ref xDoc, termNode, "Phrase",term);
                XmlDataUtil.UpdateAttribute(ref xDoc, termNode,"Count","1");
                double termWeight =
                    double.Parse(WorksheetHelper.GetCell(sheet, termWeightColIndex, rowIndex).Value2.ToString());
                XmlDataUtil.UpdateAttribute(ref xDoc, termNode, "Weight",termWeight.ToString());

                rowIndex++;
            }

            xDoc.Save(tgtXmlFilePath);

            excelWorkbook.Close(false, excelFilePath, null);
            excelApp.Quit();
        }
        #endregion

        private bool IsComboxSelected(ComboBox comboBox, string field)
        {
            if(string.IsNullOrEmpty(comboBox.Text))
            {
                MessageBox.Show("Please specify a field for "+field);
                return false;
            }
            else
            {
                return true;
            }
        }

        private void btn_Cancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}
